package org.lyq.erp.auth.employee.controller;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public class ReadEmployeeExcel {

	/**
	 * 逐行遍历其Excel
	 * @param <T>
	 * @param inputStream  excle的输入流
	 * @param error_num  错误存放集合
	 * @return
	 * @throws IOException
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */
	public static <T> Object readXls(InputStream inputStream, List<Object> errorList, Class<T> clazz)
			throws IOException, InstantiationException, IllegalAccessException {

		InputStream is = new BufferedInputStream(inputStream);
		@SuppressWarnings("resource")
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		// 类的成员属性数组
		Field[] fields = clazz.getDeclaredFields();
		
		int fieldNum = fields.length;

		List<T> list = new ArrayList<T>();

		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);

			if (hssfSheet == null) {
				continue;
			}

			for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

				HSSFRow hssfRow = hssfSheet.getRow(rowNum);

				// 检查每行的空格数，如果小于10证明有一个或多个空格，但不是整行
				if (CheckRowNull(hssfRow) < fieldNum) {

					List<HSSFCell> hssfCellList = new ArrayList<HSSFCell>();

					// 循环放入集合中
					for (int i = 0; i < fields.length; i++) {
  						hssfCellList.add(i, hssfRow.getCell(i));
					}

					int falg = 0;// 用于跳出最近的for循环
					for (int i = 0; i < fieldNum; i++) {
						// 为记录前台进行提示某行某列出错
						falg = CheckRowError(hssfCellList.get(i), errorList, rowNum, i);
						//如果falg==-1表示
						if (falg == -1) {
							return hssfCellList;
						}
					}

					// 将遍历的每一列放入 model对象中
					T t = clazz.newInstance();
					// 循环放入集合中
					for (int i = 0; i < fields.length; i++) {
						
						Field field = fields[i];
						//打开赋值权限
						field.setAccessible(true);
						
						//判定属性类型值
						Class<?> type = field.getType();
						String name = type.getName();
						
						if(name.equals("java.lang.Integer")) {
							String str=getCellValue(hssfRow.getCell(i));
							int num=Integer.parseInt(str);
							field.set(t, num);
						}else if(name.equals("java.lang.String")) {
							 field.set(t, getCellValue(hssfRow.getCell(i)));
						}else if(name.equals("java.lang.Long")) {
							field.setLong(t, Long.parseLong(getCellValue(hssfRow.getCell(i))));
						}else if(name.equals("java.lang.Double")) {
							field.setDouble(t, Double.parseDouble(getCellValue(hssfRow.getCell(i))));
						}else if(name.equals("java.lang.Float")) {
							field.setFloat(t, Float.parseFloat(getCellValue(hssfRow.getCell(i))));
						}
					}
					list.add(t);
				} else {
					continue;
				}
			}

		}
		return list;
	}

	/**
	 * 对Excel的各个单元格的格式进行判断并转换(全部转换成字符串)
	 */
	@SuppressWarnings({ "deprecation" })
	public static String getCellValue(HSSFCell cell) {
		String cellValue = "";
		DecimalFormat df = new DecimalFormat("#");
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			cellValue = cell.getRichStringCellValue().getString().trim();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			cellValue = df.format(cell.getNumericCellValue()).toString();
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			cellValue = cell.getCellFormula();
			break;
		default:
			cellValue = "";
		}
		return cellValue;
	}

	/**
	 * 判断某行某列出现问题（并且返回错误）
	 * 
	 * @param cell
	 * @param error_num
	 * @param rowNum
	 * @param cell_num
	 * @return
	 */
	@SuppressWarnings({ "deprecation" })
	public static int CheckRowError(HSSFCell cell, List<Object> errorList, int rowNum, int cell_num) {
		// 判断各个单元格是否为空
		if (cell == null || cell.equals("") || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
			errorList.add("出错啦！请检查第" + (rowNum + 1) + "行第" + (cell_num + 1) + "列。" + "如果您在该行没有数据，建议您选择删除该行，重试！");
			return -1;
		}
		return 0;
	}

	/**
	 * 判断行为空
	 * 
	 * @param hssfRow
	 * @return
	 */
	@SuppressWarnings({ "deprecation" })
	public static int CheckRowNull(HSSFRow hssfRow) {
		int num = 0;
		Iterator<Cell> cellItr = hssfRow.iterator();
		while (cellItr.hasNext()) {
			Cell c = cellItr.next();
			if (c.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				num++;
			}
		}
		return num;
	}
}
